﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.ETL
{
    public partial class OracleBulkLoadEditor : Form, IComponentEditor 
    {
        private object _OwnerActivityNode;
        private OracleBulkLoadComponent _OracleBulkLoader;
        /// <summary></summary>
        public OracleBulkLoadEditor()
        {
            InitializeComponent();
        }

        private void OracleBulkLoadEditor_Load(object sender, EventArgs e)
        {
            this.cbo_FieldDelimiter.Items.Clear();
            for(int i=0;i<=4;i++)
            {
                FieldDelimiter delimiter = (FieldDelimiter) i;
                this.cbo_FieldDelimiter.Items.Add(delimiter.ToString());
            }
        }

        public event UpdateComponentSettings UpdateComponentSettingEvent;

        public object OwnerActivityNode
        {
            get { return _OwnerActivityNode; }
            set { _OwnerActivityNode = value; }
        }

        public Type WorkflowComponentType
        {
            get { return typeof(OracleBulkLoadComponent); }
        }

        public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents,
                                 object activityNode, WorkflowComponentBase wfComponent)
        {
            this._OwnerActivityNode = activityNode;
            this._OracleBulkLoader = (OracleBulkLoadComponent) wfComponent;

            this.txt_SqlldrFilePath.Text = this._OracleBulkLoader.SqlLoaderProgramFilePath;
            this.txt_ConnStr.Text = this._OracleBulkLoader.ConnectionString;
            this.cbo_Tables.Items.Clear();
            this.cbo_Tables.SelectedIndex = -1;
            this.cbo_Tables.Text = string.Empty;
            this.txt_SrcConnStr.Text = this._OracleBulkLoader.SrcConnStr;
            this.rt_SelectSql.Text = this._OracleBulkLoader.SelectSqlStatement;
            this.ck_TruncateBeforeLoad.Checked = this._OracleBulkLoader.TruncateTableBeforeInsert;

            if(!string.IsNullOrEmpty(this._OracleBulkLoader.ConnectionString))
            {
                DbSchemaCache.DbSchemaCacheKey key = new DbSchemaCache.DbSchemaCacheKey();
                key.ConnStr = this._OracleBulkLoader.ConnectionString;
                key.DbProvider = DataProviderType.Oracle;
                key.SelectStatement = string.Empty;
                
                DbSchemaCache schemaCache = new DbSchemaCache();
                List<string> tblNames = schemaCache.GetTables(key);
                if(tblNames !=null && tblNames.Count>0)
                {
                    foreach(string tblName in tblNames)
                    {
                        this.cbo_Tables.Items.Add(tblName);
                    }
                }
            }
            if(!string.IsNullOrEmpty(this._OracleBulkLoader.TableName))
            {
                bool found = false;
                if(this.cbo_Tables.Items.Count>0)
                {
                    for(int i=0;i<this.cbo_Tables.Items.Count;i++)
                    {
                        if(this.cbo_Tables.Items[i].ToString()==this._OracleBulkLoader.TableName)
                        {
                            found = true;
                            this.cbo_Tables.SelectedIndex = i;
                            break;
                        }
                    }
                }

                if (!found)
                {
                    this.cbo_Tables.Text = this._OracleBulkLoader.TableName;
                }
            }
            this.txt_SrcFilePath.Text = this._OracleBulkLoader.SrcDataFilePath;
            if(this._OracleBulkLoader.FieldSizes !=null && this._OracleBulkLoader.FieldSizes.Count>0)
            {
                this.rd_FixedSize.Checked = true;
            }
            else if(this._OracleBulkLoader.FieldDelimiter !=FieldDelimiter.None)
            {
                this.rd_Delimited.Checked = true;
                this.cbo_FieldDelimiter.SelectedIndex = (int) this._OracleBulkLoader.FieldDelimiter;
            }

            this.dv_TableFields.Rows.Clear();
            if(!string.IsNullOrEmpty(this._OracleBulkLoader.TableName))
            {
                DbSchemaCache.DbSchemaCacheKey key = new DbSchemaCache.DbSchemaCacheKey();
                key.ConnStr = this._OracleBulkLoader.ConnectionString;
                key.DbProvider = DataProviderType.Oracle;
                key.SelectStatement = string.Empty;
                DbSchemaCache schemaCache = new DbSchemaCache();
                List<TableColumn> cols = schemaCache.GetColumns(key, this._OracleBulkLoader.TableName, string.Empty);
                TableColumn[] srcCols = null;
                if (!string.IsNullOrEmpty(this._OracleBulkLoader.SrcConnStr) && !string.IsNullOrEmpty(this._OracleBulkLoader.SelectSqlStatement))
                {
                    srcCols = DbUtil.GetTableColumns(DataProviderType.MSSQL, this._OracleBulkLoader.SrcConnStr, this._OracleBulkLoader.SelectSqlStatement);
                }
                if (srcCols != null && srcCols.Length > 0 && cols !=null && cols.Count>0)
                {
                    this.InitFieldMappings(srcCols, cols.ToArray());
                }
            }
        }


        /// <summary>
        /// 
        /// </summary>
        public void SaveSettings()
        {
            this._OracleBulkLoader.SqlLoaderProgramFilePath = this.txt_SqlldrFilePath.Text;
            this._OracleBulkLoader.ConnectionString = this.txt_ConnStr.Text;
            this._OracleBulkLoader.TableName = this.cbo_Tables.Text;
            this._OracleBulkLoader.SrcDataFilePath = this.txt_SrcFilePath.Text;
            this._OracleBulkLoader.FieldDelimiter = (FieldDelimiter) this.cbo_FieldDelimiter.SelectedIndex;
            this._OracleBulkLoader.SrcConnStr = this.txt_SrcConnStr.Text;
            this._OracleBulkLoader.SelectSqlStatement = this.rt_SelectSql.Text;
            this._OracleBulkLoader.TruncateTableBeforeInsert = this.ck_TruncateBeforeLoad.Checked;

            this._OracleBulkLoader.FieldSizes = this.GetSelectedTableFieldSizes();
            this._OracleBulkLoader.TableFields = this.GetSelectedTableFields();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public TestResult Test()
        {
            this.SaveSettings();
            OracleBulkLoadWorker worker=new OracleBulkLoadWorker();
            worker.SetInstruction(Guid.Empty,this._OracleBulkLoader.Instruction, string.Empty, this._OracleBulkLoader.ComponentName);
            worker.WorkerErredEvent += new WorkerHasError(worker_WorkerErredEvent);
            try
            {
                worker.DoWork();
                return new TestResult(true, "Success");
            }
            catch(Exception ex)
            {
                return new TestResult(false,ex.ToString());
            }
        }

        void worker_WorkerErredEvent(Guid ticketID, Type workerType, InstructionBase instructionBase, string error, bool isFatal)
        {
            MessageBox.Show(error);
        }

        private void btn_Refresh_Click(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(this.txt_ConnStr.Text))
            {
                DbSchemaCache.DbSchemaCacheKey key = new DbSchemaCache.DbSchemaCacheKey();
                key.ConnStr = this.txt_ConnStr.Text;
                key.DbProvider = DataProviderType.Oracle;
                key.SelectStatement = string.Empty;
                DbSchemaCache schemaCache = new DbSchemaCache();
                List<string> tableNames = schemaCache.GetTables(key);
                this.cbo_Tables.Items.Clear();
                this.cbo_Tables.SelectedIndex = -1;
                this.cbo_Tables.Text = string.Empty;

                if(tableNames !=null && tableNames.Count>0)
                {
                    foreach(string tblName in tableNames)
                    {
                        this.cbo_Tables.Items.Add(tblName);
                    }
                }

                if(this.cbo_Tables.Items.Count>0 && (!string.IsNullOrEmpty(this._OracleBulkLoader.TableName)))
                {
                    for(int i=0;i<this.cbo_Tables.Items.Count;i++)
                    {
                        if(this.cbo_Tables.Items[i].ToString()==this._OracleBulkLoader.TableName)
                        {
                            this.cbo_Tables.SelectedIndex = i;
                            break;
                        }
                    }
                }
            }
        }

        private void cbo_Tables_SelectedIndexChanged(object sender, EventArgs e)
        {
            List<TableColumn> tgtCols=new List<TableColumn>();
            if(!string.IsNullOrEmpty(this.txt_ConnStr.Text) && this.cbo_Tables.SelectedIndex>=0)
            {
                string tblName = this.cbo_Tables.SelectedItem.ToString();
                DbSchemaCache.DbSchemaCacheKey key = new DbSchemaCache.DbSchemaCacheKey();
                key.ConnStr = this.txt_ConnStr.Text;
                key.DbProvider = DataProviderType.Oracle;
                key.SelectStatement = string.Empty;
                DbSchemaCache schemaCache = new DbSchemaCache();
                tgtCols = schemaCache.GetColumns(key, tblName, string.Empty);
            }
            List<TableColumn> srcCols=new List<TableColumn>();
            if(!string.IsNullOrEmpty(this.txt_SrcConnStr.Text) && !string.IsNullOrEmpty(this.rt_SelectSql.Text))
            {
                TableColumn[] srcColumns = DbUtil.GetTableColumns(DataProviderType.MSSQL,this.txt_SrcConnStr.Text,this.rt_SelectSql.Text);
                if(srcColumns !=null && srcColumns.Length>0)
                {
                    srcCols.AddRange(srcColumns);
                }
            }
            if(tgtCols.Count>0 && srcCols.Count>0)
            {
                this.InitFieldMappings(srcCols.ToArray(), tgtCols.ToArray());
            }
        }

        private void dv_TableFields_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if(e.RowIndex>=0 && e.ColumnIndex>=2 && e.ColumnIndex<=3)
            {
                DataGridViewRow dr = this.dv_TableFields.Rows[e.RowIndex];
                if(!dr.IsNewRow)
                {
                    if (e.ColumnIndex == 2 && e.RowIndex > 0)
                    {
                        object fieldNameCopy = this.dv_TableFields.Rows[e.RowIndex].Cells[0].Value;
                        object selectedCopy  =
                            this.dv_TableFields.Rows[e.RowIndex].Cells[1].Value;
                        object fieldSizeCopy = this.dv_TableFields.Rows[e.RowIndex].Cells[4].Value;

                        this.dv_TableFields.Rows[e.RowIndex].Cells[0].Value =
                            this.dv_TableFields.Rows[e.RowIndex - 1].Cells[0].Value;
                        this.dv_TableFields.Rows[e.RowIndex].Cells[1].Value =
                            this.dv_TableFields.Rows[e.RowIndex - 1].Cells[1].Value;
                        this.dv_TableFields.Rows[e.RowIndex].Cells[4].Value =
                            this.dv_TableFields.Rows[e.RowIndex - 1].Cells[4].Value;

                        this.dv_TableFields.Rows[e.RowIndex - 1].Cells[0].Value = fieldNameCopy;
                        this.dv_TableFields.Rows[e.RowIndex - 1].Cells[1].Value = selectedCopy;
                        this.dv_TableFields.Rows[e.RowIndex - 1].Cells[4].Value = fieldSizeCopy;

                        this.dv_TableFields.Rows[e.RowIndex].Cells[e.ColumnIndex].Selected = false;
                        this.dv_TableFields.Rows[e.RowIndex - 1].Selected = true;
                    }
                    else if (e.ColumnIndex == 3 && e.RowIndex < this.dv_TableFields.Rows.Count - 1)
                    {
                        object fieldNameCopy = this.dv_TableFields.Rows[e.RowIndex].Cells[0].Value;
                        object selectedCopy =
                            this.dv_TableFields.Rows[e.RowIndex].Cells[1].Value;
                        object fieldSizeCopy = this.dv_TableFields.Rows[e.RowIndex].Cells[4].Value;

                        this.dv_TableFields.Rows[e.RowIndex].Cells[0].Value =
                            this.dv_TableFields.Rows[e.RowIndex + 1].Cells[0].Value;
                        this.dv_TableFields.Rows[e.RowIndex].Cells[1].Value =
                            this.dv_TableFields.Rows[e.RowIndex + 1].Cells[1].Value;
                        this.dv_TableFields.Rows[e.RowIndex].Cells[4].Value =
                            this.dv_TableFields.Rows[e.RowIndex + 1].Cells[4].Value;

                        this.dv_TableFields.Rows[e.RowIndex + 1].Cells[0].Value = fieldNameCopy;
                        this.dv_TableFields.Rows[e.RowIndex + 1].Cells[1].Value = selectedCopy;
                        this.dv_TableFields.Rows[e.RowIndex + 1].Cells[4].Value = fieldSizeCopy;

                        this.dv_TableFields.Rows[e.RowIndex].Cells[e.ColumnIndex].Selected = false;
                        this.dv_TableFields.Rows[e.RowIndex + 1].Selected = true;
                    }
                }
            }
        }

        private void btn_Browse2_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg=new OpenFileDialog();
            dlg.Filter = "Executable(*.exe)|*.exe|All files(*.*)|*.*";
            if(dlg.ShowDialog()==DialogResult.OK)
            {
                this.txt_SqlldrFilePath.Text = dlg.FileName;
            }
        }

        private void btn_Browse_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg=new OpenFileDialog();
            if(dlg.ShowDialog()==DialogResult.OK)
            {
                this.txt_SrcFilePath.Text = dlg.FileName;
                List<string> fieldNames = this.GetSelectedTableFields();
                Dictionary<string, int> fieldSizes = this.GetSelectedTableFieldSizes();
                FieldDelimiter delimiter = (FieldDelimiter) this.cbo_FieldDelimiter.SelectedIndex;
                if(fieldNames !=null && fieldNames.Count>0)
                {
                    DataTable dtPreview =
                        this.ParseFileIntoTable(this.txt_SrcFilePath.Text, fieldNames, fieldSizes, delimiter , 50);
                    this.dv_DataPreview.DataSource = dtPreview;
                }
            }
        }

        private DataTable ParseFileIntoTable(string filePath, List<string> fieldNames, Dictionary<string,int> fieldSizes, FieldDelimiter delimiter, int maxRowCount)
        {
            DataTable dt=new DataTable();
            foreach(string fieldName in fieldNames)
            {
                dt.Columns.Add(fieldName, typeof (string));
            }

            int rowCount = 0;
            StreamReader reader=new StreamReader(filePath);
            while(!reader.EndOfStream)
            {
                string line = reader.ReadLine();
                if(fieldSizes !=null && fieldSizes.Count>0)
                {
                    DataRow dr = dt.NewRow();
                    int pos = 0;
                    for(int i=0;i<fieldNames.Count;i++)
                    {
                        int fieldSize = fieldSizes[fieldNames[i]];
                        if(line.Length >=pos+fieldSize)
                        {
                            string fieldValue = line.Substring(pos, fieldSize);
                            dr[i] = fieldValue;
                            pos = pos + fieldSize;
                        }
                        else
                        {
                            break;
                        }
                    }
                    dt.Rows.Add(dr);
                }
                else if(delimiter !=FieldDelimiter.None)
                {
                    DataRow dr = dt.NewRow();
                    string[] fieldValues = null;
                    switch(delimiter)
                    {
                        case FieldDelimiter.Tab:
                            fieldValues = line.Split(new char[] {'\t'});
                            break;
                        case FieldDelimiter.Comma:
                            fieldValues = line.Split(new char[] {','});
                            break;
                        case FieldDelimiter.Space:
                            fieldValues = line.Split(new char[] {' '});
                            break;
                        case FieldDelimiter.VerticalBar:
                            fieldValues = line.Split(new char[] { '|' });
                            break;
                        default:
                            break;
                    }
                    if (fieldValues != null)
                    {
                        if (fieldValues.Length >= fieldNames.Count)
                        {
                            for (int i = 0; i < fieldNames.Count; i++)
                            {
                                if (fieldValues[i].Length > 0)
                                {
                                    dr[i] = fieldValues[i];
                                }
                                else
                                {
                                    dr[i] = null;
                                }
                            }
                        }
                        else
                        {
                            for (int i = 0; i < fieldValues.Length; i++)
                            {
                                if (fieldValues[i].Length > 0)
                                {
                                    dr[i] = fieldValues[i];
                                }
                                else
                                {
                                    dr[i] = null;
                                }
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                rowCount++;
                if(maxRowCount>0 && rowCount>maxRowCount)
                {
                    break;
                }
            }
            reader.Close();
            return dt;
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            this.SaveSettings();
            if(this.UpdateComponentSettingEvent !=null)
            {
                this.UpdateComponentSettingEvent(this._OwnerActivityNode, this._OracleBulkLoader);
            }
            this.Close();
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_Test_Click(object sender, EventArgs e)
        {
            TestResult result = this.Test();
            MessageBox.Show(result.Message);
        }

        #region field mappings
        private void InitFieldMappings(TableColumn[] srcCols, TableColumn[] tgtCols)
        {
            this.dv_TableFields.Rows.Clear();
            foreach (TableColumn srcCol in srcCols)
            {
                DataGridViewRow dr = new DataGridViewRow();
                DataGridViewTextBoxCell cellSrcCol = new DataGridViewTextBoxCell();
                cellSrcCol.Value = srcCol.ColumnName;
                dr.Cells.Add(cellSrcCol);
                DataGridViewComboBoxCell cellTgt = new DataGridViewComboBoxCell();
                foreach (TableColumn tgtCol in tgtCols)
                {
                    cellTgt.Items.Add(tgtCol.ColumnName);
                    if (tgtCol.ColumnName.ToLower() == srcCol.ColumnName.ToLower())
                    {
                        cellTgt.Value = tgtCol.ColumnName;
                    }
                }
                dr.Cells.Add(cellTgt);
                DataGridViewImageCell cellUp = new DataGridViewImageCell();
                cellUp.Value = this.imageList1.Images[0];
                dr.Cells.Add(cellUp);
                DataGridViewImageCell cellDown = new DataGridViewImageCell();
                cellDown.Value = this.imageList1.Images[1];
                dr.Cells.Add(cellDown);
                DataGridViewTextBoxCell cellSize = new DataGridViewTextBoxCell();
                cellSize.Value = null;
                dr.Cells.Add(cellSize);
                this.dv_TableFields.Rows.Add(dr);
            }
        }

        private List<string> GetSelectedTableFields()
        {
            List<string> selectedColumnNames = new List<string>();
            foreach (DataGridViewRow dr in this.dv_TableFields.Rows)
            {
                if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[1].Value != null)
                {
                    string srcColName = dr.Cells[0].Value.ToString();
                    string tgtColName = dr.Cells[1].Value.ToString();
                    if (!string.IsNullOrEmpty(srcColName) && !string.IsNullOrEmpty(tgtColName))
                    {
                        selectedColumnNames.Add(tgtColName);
                    }
                }
            }
            return selectedColumnNames;
        }

        private Dictionary<string, int> GetSelectedTableFieldSizes()
        {
            Dictionary<string, int> fieldSizes = new Dictionary<string, int>();
            if (this.dv_TableFields.Rows.Count > 0)
            {
                foreach (DataGridViewRow dr in this.dv_TableFields.Rows)
                {
                    if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[4].Value != null)
                    {
                        string fieldName = dr.Cells[0].Value.ToString();
                        int fieldSize = int.Parse(dr.Cells[4].Value.ToString());
                        if (fieldSize > 0)
                        {
                            fieldSizes.Add(fieldName, fieldSize);
                        }
                    }
                }
            }
            return fieldSizes;
        }

        #endregion
    }
}
